--Requete pour methode GetQuestionnaires(int idCategorie)
select q.Id as id, q.Nom as titre
from Questionnaires q
where q.IdCat = 2 --@idCategorie

--Requete pour methode GetQuestionnaire(int idQuestionnaire)
declare @idQuestionnaire int 
set @idQuestionnaire = 1

declare @nbQuestions int
set @nbQuestions =
(select COUNT(qu.IdQuestion) as nbQuestions
from Questions qu
where qu.IdQuestionnaire = 1)

select Id as id, Nom as titre, Niveau as level, Description as descritpion, @nbQuestions as nbQuestions
from Questionnaires
where Id = @idQuestionnaire

--Requete pour la methode GetQuestions(int idQuestion)
declare @idQuestionnaire2 int 
set @idQuestionnaire2 = 1
select IdQuestion, Intitule
from Questions
where IdQuestionnaire = @idQuestionnaire2

select r.IdQuestion, r.IdReponse, r.Libele 
from Reponses r
where IdQuestion in
	(select q3.IdQuestion from Questions q3 where q3.IdQuestionnaire = @idQuestionnaire2)
order by r.IdQuestion, r.IdReponse


--Requete pour la methode Correction(int idQuestionnaire)
declare @idQuestionnaire2 int
set @idQuestionnaire2 = 1

declare @stringResult varchar(max)
set @stringResult = '1,3,5,7,18'

IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'tabletemp' AND type = 'U')
		Begin
			Drop table tempdb.dbo.tabletemp					
		END		
If NOT EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'tabletemp' AND type = 'U')
	Begin
			create table tempdb.dbo.tabletemp (reponse varchar(5))
	end

while charindex(',', @stringResult) > 0
begin
	Insert into tempdb.dbo.tabletemp values (substring(@stringResult, 0, charindex(',', @stringResult))) 
	set @stringResult = SUBSTRING(@stringResult, charindex(',', @stringResult)+1, LEN(@stringResult))
end
Insert into tempdb.dbo.tabletemp values (substring(@stringResult, 1, LEN(@stringResult))) 

--select * from tempdb.dbo.tabletemp
select sum(correct.note) as NoteExamen
from
(
	select 
		case sum(r.Note) 
			when 100 then 1
			else -2
		end as note
	from tempdb.dbo.tabletemp tmp
	inner join Reponses r on r.IdReponse = tmp.reponse
	group by r.IdQuestion
)
as correct
